CREATE OR REPLACE FUNCTION import_coned_tickets() RETURNS VOID AS '
DECLARE
   -- We iterate over this cursor to get each 
   -- tickets info and then insert into 3 GATE tables:
   -- t_lang_resource, t_doc_content and t_document.
   cur CURSOR FOR 
       SELECT ticket, act, remarks
           FROM "be_ecstickets_1996_2006_axinia"
           ORDER BY ticket;
           
   vTicketNumber CHARACTER VARYING(10);
   vTroubleType CHARACTER VARYING(10);
   vRemarks TEXT;
   vLrID INTEGER;
   vDcID INTEGER;
   vDocID INTEGER;
BEGIN
   -- Open cursor
    OPEN cur;
    LOOP
        FETCH cur INTO vTicketNumber, vTroubleType, vRemarks;
        IF  NOT FOUND THEN
            EXIT;  -- exit loop
        END IF;
        SELECT nextval(''seq_lang_resource'') INTO vLrID;
        
        INSERT INTO t_lang_resource(lr_id 
            , lr_owner_user_id , lr_owner_group_id 
            , lr_locking_user_id, lr_type_id 
            , lr_name , lr_access_mode , lr_parent_id)
        VALUES(vLrID, 0, 0, null, 1, vTicketNumber, 1, null);
        
        
        SELECT nextval(''seq_doc_content'') INTO vDcID;
        
        INSERT INTO t_doc_content(dc_id , dc_encoding_id , dc_character_content, dc_binary_content , dc_content_type)
        VALUES(vDcID, 2, vRemarks, null, 1);
        
        
        SELECT nextval(''seq_document'') INTO vDocID;
        
        INSERT INTO t_document (doc_id , doc_content_id , doc_lr_id , doc_url , doc_is_markup_aware)
        VALUES(vDocID, vDcID, vLrID, ''file:/D:/StrayVoltage/BE/'' || vTroubleType || ''/'' || vTicketNumber || ''.txt'', ''t'');
        
    END LOOP;
    CLOSE cur;

END;

' LANGUAGE 'plpgsql';


-- SELECT import_coned_tickets();